<?php

class Canbo extends Model
{
    public function login($params, $encryptedPass = false)
    {
        $arg['user'] = $params['user'];
        $arg['pass'] = $params['pass'];
        if (!$encryptedPass) {
            $arg['pass'] = generatePwd($params['pass']);
        }
        $check = $this->select('cb_id = :user AND cb_matkhau = :pass', $arg);
        if (count($check) == 1) {
            $check = $check[0];
            $_SESSION['user']['id'] = $check['cb_id'];
            $_SESSION['user']['hoten'] = $check['cb_hoten'];
            $_SESSION['user']['type'] = $check['g_id'] == 2 ? 'admin' : 'user';
            return true;
        }
        return false;
    }

    public function changePassword($new)
    {
        # Check old pass
        $this->update(array('cb_matkhau' => generatePwd($new)), 'cb_id = :id',
            array('id' => $_SESSION['user']['id']));
    }

    public function getInfo($id)
    {
        $appConfig = $GLOBALS['config']->app;

        #$info = $this->select('cb_id=:id', array('id' => $id));
        $info = $this->_db->run('SELECT * FROM canbo NATURAL JOIN ngach WHERE cb_id = :id', array('id' => $id));
        if (count($info) > 0) {
            $info = $info[0];
        }
        $info['cb_gtinh'] = $info['cb_gtinh'] == 0 ? 'Nữ' : 'Nam';
        $info['cb_nsinh'] = @date('d/m/Y', $info['cb_nsinh']);
        if (!file_exists(ROOT . DS . $appConfig->img_folder . '/' . $info['cb_hinh'])) {
            $info['cb_hinh'] = $appConfig->img_default;
        }

        $info['cb_hinh'] = $appConfig->img_folder . '/' . $info['cb_hinh'];

        # Xác định thông tin cán bộ hành chính hoặc cán bộ giảng dạy
        $sql = <<<SQL
SELECT bm_ten as cb_bomon, dv_ten as cb_khoa, cd_mota as cb_chucdanh, dv_id, cd_id
FROM cbgiangday NATURAL JOIN bomon NATURAL JOIN donvi NATURAL JOIN chucdanh
WHERE cbgiangday.cb_id = :id;
SQL;

        $result = $this->_db->run($sql, array('id' => $id));
        if (count($result) > 0) {
            $info = array_merge($info, $result[0]);
        } else {
            $info['cbgiangday'] = 0;
            $sql = <<<SQL
SELECT dv_ten AS cb_phongban, nv_mota AS cb_nghiepvu, dv_id, nv_id
FROM cbhanhchinh NATURAL JOIN nghiepvu NATURAL JOIN donvi
WHERE cb_id = :id
SQL;

            $result = $this->_db->run($sql, array('id' => $id));
            if (count($result) > 0) {
                $info = array_merge($info, $result[0]);
            }
        }
        return $info;
    }

    public function getCongTac($id)
    {
        $sql = 'SELECT canbo.cb_id, donvi.dv_ten as donvi, chucvu.cv_mota as chucvu, ' .
               'DATE_FORMAT(quatrinhct.qtct_ngaybd, "%d/%m/%Y") as tgian ' .
               'FROM quatrinhct NATURAL JOIN chucvu NATURAL JOIN canbo NATURAL JOIN donvi ' .
               'WHERE canbo.cb_id = :id';
        return $this->_db->run($sql, array('id' => $id));
    }

    public function getChucVu($cb_id, $dv_id)
    {
        $sql = <<<SQL
        SELECT cv_mota
FROM canbo NATURAL JOIN quatrinhct NATURAL JOIN donvi NATURAL JOIN chucvu
WHERE canbo.cb_id = :cb_id AND donvi.dv_id = :dv_id;
SQL;
        return $this->_db->executeScalar($sql, array(':cb_id' => $cb_id, 'dv_id' => $dv_id));
    }

    public function getHopDong($id)
    {
        $sql = 'SELECT lhd_id as hd_loai_id, lhd_mota as hd_loai, ' .
               'DATE_FORMAT(hd_ngaybd, "%d-%m-%Y") as hd_ngaybd, ' .
               'IF(lhd_id = 2, "không", DATE_FORMAT(hd_ngaykt, "%d-%m-%Y")) as hd_ngaykt, ' .
               'cthd_gchu as hd_ghichu ' .
               'FROM loai_hopdong NATURAL JOIN hopdong NATURAL JOIN chitiethd NATURAL JOIN canbo ' .
               'WHERE canbo.cb_id = :id;';
        return $this->_db->run($sql, array('id' => $id));
    }

    public function getKhenThuong($id)
    {
        $sql = 'SELECT kt_mota, DATE_FORMAT(kt_ngay, "%d/%m/%Y") AS kt_ngay ' .
               'FROM khenthuong ' .
               'WHERE cb_id = :id;';
        return $this->_db->run($sql, array('id' => $id));
    }

    public function getKyLuat($id)
    {
        $sql = 'SELECT kl_mota, DATE_FORMAT(kl_ngay, "%d/%m/%Y") AS kl_ngay ' .
            'FROM kyluat ' .
            'WHERE cb_id = :id;';
        return $this->_db->run($sql, array('id' => $id));
    }

    public function getFullInfo($id)
    {
        $sql = <<<SQL
SELECT cb_id, cb_hoten, cb_gtinh,
  DATE_FORMAT(cb_nsinh, "%d/%m/%Y") AS cb_nsinh, cb_sodt, cb_kethon, g_id,
		 cb_diachi, cb_email, cb_hinh, dt_id, tg_id, td_id, hv_id, hh_id, b_id, n_id
FROM canbo
WHERE cb_id = :id AND cb_trangthai = 1
SQL;
        return $this->_db->run($sql, array('id' => $id));

    }

    public function selectById($id)
    {
        return $this->select('cb_id = :cb_id', array('cb_id' => $id));
    }

    public function del($id)
    {
        return $this->update(array('cb_trangthai' => 0), 'cb_id = :id', array('id' => $id));
    }

    public function selectAll()
    {
        return $this->select('cb_trangthai = 1');
    }

    public function add()
    {
        $_POST['cb_id'] = $this->generateNewId();
        $this->insert($_POST);
        return $_POST['cb_id'];
    }

    private function  generateNewId()
    {
        $max = $this->_db->executeScalar('select MAX(cb_id) as MID FROM canbo');
        $newId = '00001';
        if ($max) {
            $max = (int)$max + 1;
            $newId = str_pad($max, 5, '0', STR_PAD_LEFT);
        }
        return $newId;
    }

    # Kiểm tra cán bộ có $id có phải là cán bộ hành chính
    public function hanhchinh($id)
    {
        $sql = "SELECT COUNT(*) FROM cbhanhchinh WHERE cb_id = :id";
        return $this->_db->executeScalar($sql, array('id' => $id));
    }

    public function giangday($id)
    {
        $sql = "SELECT COUNT(*) FROM cbgiangday WHERE cb_id = :id";
        return $this->_db->executeScalar($sql, array('id' => $id));
    }

    /**
     * thực hiện gỡ bỏ cán bộ khỏi đơn vị cũ trước khi thuyên chuyển
     * @param $id
     */
    private function pre_thuyenchuyen($id)
    {
        if ($this->giangday($id)) {
            $sql = <<<SQL
DELETE FROM cbgiangday WHERE cb_id = :id
SQL;
            $this->_db->run($sql, array('id' => $id));

        } elseif ($this->hanhchinh($id)) {
            $sql = <<<SQL
DELETE FROM cbhanhchinh WHERE cb_id = :id
SQL;
            $this->_db->run($sql, array('id' => $id));
        }
    }

    private function update_qtct($id, $cv_id, $dv_id)
    {
        # Thêm dữ liệu vào bảng quá trình công tác
        $sql = <<<SQL
INSERT INTO quatrinhct(cb_id, cv_id, dv_id, qtct_ngaybd) VALUES(:id, :cv_id, :dv_id, NOW())
SQL;
        try {
            $this->_db->run($sql, array('id' => $id, 'cv_id' => $cv_id, 'dv_id' => $dv_id));
        } catch (Exception $ex) {
            if ($ex->getCode() != 23000) {
                throw $ex;
            }
        }
    }

    /**
     * @param $id string ID của cán bộ cần thuyên chuyển
     * @param $cd_id int ID chức danh mới
     * @param $k_id int ID khoa mới
     * @param $bm_id int ID bộ môn mới
     * @param $cv_id int ID chức vụ mới
     */
    public function thuyenchuyen_giangday($id, $cd_id, $k_id, $bm_id, $cv_id)
    {
        $bm = new Bomon();
        $bm->fix($k_id);
        $this->pre_thuyenchuyen($id);
        $sql = <<<SQL
INSERT INTO cbgiangday(cb_id, bm_id, cd_id) VALUES(:id, :bm_id, :cd_id);
SQL;
        $this->_db->run($sql, array('id' => $id, 'bm_id' => $bm_id, 'cd_id' => $cd_id));

        # Thêm dữ liệu vào bảng quá trình công tác
        $this->update_qtct($id, $cv_id, $k_id);
    }

    public function thuyenchuyen_hanhchinh($id, $nv_id, $dv_id, $cv_id)
    {
        $this->pre_thuyenchuyen($id);
        $sql = <<<SQL
INSERT INTO cbhanhchinh VALUES(:id, :nv_id, :dv_id)
SQL;
        $this->_db->run($sql, array('id' => $id, 'nv_id' => $nv_id, 'dv_id' => $dv_id));

        $this->update_qtct($id, $cv_id, $dv_id);

    }

    public function getList()
    {
        $sql = <<<SQL
SELECT cb.cb_id AS cb_id, cb_hoten,
  IF (cb_gtinh = 1, 'Nữ', 'Nam') AS cb_gtinh
  , DATE_FORMAT(cb_nsinh, '%d/%m/%Y') AS cb_nsinh, tg_ten AS cb_tongiao,
	dt_ten AS cb_dantoc, td_mota AS cb_trinhdo, dv_ten AS cb_donvi, cb_email
FROM
	canbo cb NATURAL JOIN trinhdo td NATURAL JOIN dantoc dt NATURAL JOIN tongiao
	LEFT JOIN cbgiangday cbgd ON cb.cb_id = cbgd.cb_id
	LEFT JOIN cbhanhchinh cbhc ON cb.cb_id = cbhc.cb_id
	LEFT JOIN bomon bm ON bm.bm_id = cbgd.bm_id
	LEFT JOIN khoa k ON k.dv_id = bm.dv_id
	LEFT JOIN phongban pb ON pb.dv_id = cbhc.dv_id
	LEFT JOIN donvi dv ON dv.dv_id = pb.dv_id OR dv.dv_id = k.dv_id
ORDER BY cb_hoten
SQL;
        return $this->_db->run($sql);
    }

    public function getNgoaiNgu($id)
    {
        $sql = <<<Sql
SELECT nn_ten
FROM ngoaingu nn NATURAL JOIN cb_ngoaingu cb
WHERE cb.cb_id = :id;
Sql;

        $result = $this->_db->run($sql, array('id' => $id));

        $text = '';
        if ($result) {
            foreach ($result as $row) {
                $text = ', ' . $row['nn_ten'] . $text;
            }
        }

        return substr($text, 1);
    }
}